package com.oms.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.liudecai.utils.jdbc.TxQueryRunner;
import com.oms.bean.User;

public class UserDao {
	QueryRunner qr=null;
	public UserDao() {
		qr = new TxQueryRunner();
	}
	public void Add(User user) throws SQLException {
		String sql = "insert into oms_user(user_id,username,password,real_name,status,email,phone,address,id_card,age,sex,postcode) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		qr.update(sql, user.getUser_id(),user.getUsername(),user.getPassword(),user.getReal_name(),user.getStatus(),user.getEmail(),user.getPhone(),user.getAddress(),user.getId_card(),user.getAge(),user.getSex(),user.getPostcode());
	}

	public void Delete(int id) throws SQLException {
		String sql = "delete from oms_user where id=?";
		qr.update(sql, id);
	}

	public void Update(User user) throws SQLException {
		String sql = "update oms_user set user_id=?,username=?,password=?,real_name=?,status=?,email=?,phone=?,address=?,id_card=?,age=?,sex=?,postcode=? where id=?";
		qr.update(sql, user.getUser_id(),user.getUsername(),user.getPassword(),user.getReal_name(),user.getStatus(),user.getEmail(),user.getPhone(),user.getAddress(),user.getId_card(),user.getAge(),user.getSex(),user.getPostcode(),user.getId());
	}

	public User FindById(int id) throws SQLException {
		String sql = "select * from oms_user where id=?";
		return qr.query(sql, new BeanHandler<>(User.class), id);

	}

	public List<User> FindByString(String str) throws SQLException {
		String sql = "select * from oms_user where ";
		return qr.query(sql, new BeanListHandler<>(User.class));

	}

	public List<User> FindAll() throws SQLException {
		String sql = "select * from oms_user";
		return qr.query(sql, new BeanListHandler<>(User.class));
	}

	public long UserCount() throws SQLException {
		String sql = "select count(id) from oms_user";
		return qr.query(sql, new ScalarHandler<>());
	}

}
